package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MRefList;
import org.compiere.model.Mhrmeducation;
import org.compiere.model.X_hrm_education;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.X_HR_Department;
import org.eevolution.model.X_HR_Employee;
import org.eevolution.model.X_HR_Job;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class ReportEducationPrint extends SvrProcess {
	
	/** Current context		*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	/** */
	private int HR_Department_ID = 0;
	/** */
	private int HR_Block_ID = 0;
	/** */
	private String path = "";
	/** */
	X_HR_Department department = null;
	/** */
	X_HR_Department block = null;
	/**	Education List	*/
	private Mhrmeducation[] lines;

	@Override
	protected void prepare() {
		
		log.info(" Report Education ");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (name == null); 
				//
			else if (name.compareTo("HR_Block_ID") == 0 && para[i].getParameter() != null)
				HR_Block_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.compareTo("HR_Department_ID") == 0 && para[i].getParameter() != null)
				HR_Department_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.toLowerCase().endsWith("directory"))
				path = (String)para[i].getParameter();
			else
			{
				log.log(Level.INFO, "Unknown Parameter: " + name);
			}
		}
		
		if(path.isEmpty())
			path = Util.localFilePath;
		else
			path = path + System.getProperty("file.separator");

		department = new X_HR_Department(getCtx(), HR_Department_ID, null);
		block = new X_HR_Department(getCtx(), HR_Block_ID, null);

	}

	@Override
	protected String doIt() throws Exception {

		// Types of cells for
		Label label;
		Number number;
		// There are checks for a specified pattern
		String templateName = "SampleEducation";
		
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);
				
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path); 
		fullPath.append("List of Education").append(fileExtension);
				
		// 
		File templateCopy = new File(fullPath.toString());
		File temporaryFile = entry.getFile(Util.localFilePath+templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		 try {
			 tableBook = Workbook.getWorkbook(temporaryFile);
			 copy = Workbook.createWorkbook(templateCopy, tableBook);
			 sheet = copy.getSheet(0);
		} catch (Exception e) {
		  	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
		   	return "Error tableBook.";
		}

		ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
		
		// Standart style cell
	    WritableCellFormat borderStyle = new WritableCellFormat();
	    borderStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    borderStyle.setAlignment(Alignment.CENTRE);
	    borderStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    borderStyle.setWrap(true);
	    // Vacation style cell
	    WritableFont fontDepartment = new WritableFont (WritableFont.TIMES, 12, WritableFont.BOLD);
	    WritableCellFormat cellDepartment = new WritableCellFormat(fontDepartment);
	    cellDepartment.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    cellDepartment.setAlignment(Alignment.CENTRE);
	    cellDepartment.setVerticalAlignment(VerticalAlignment.CENTRE);
	    cellDepartment.setBackground(Colour.TAN);
		// Title style cell
		WritableFont fontTitle = new WritableFont (WritableFont.TIMES, 14, WritableFont.NO_BOLD);
		WritableCellFormat titleStyle = new WritableCellFormat(fontTitle);
		titleStyle.setAlignment(Alignment.CENTRE);
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		// Percent format
		WritableCellFormat percentf = new WritableCellFormat(fontDepartment, NumberFormats.PERCENT_INTEGER);
		percentf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		percentf.setAlignment(Alignment.CENTRE);
		percentf.setVerticalAlignment(VerticalAlignment.CENTRE);
		//
		WritableCellFormat cellPercent = new WritableCellFormat(fontDepartment);
		cellPercent.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		cellPercent.setAlignment(Alignment.CENTRE);
		cellPercent.setVerticalAlignment(VerticalAlignment.CENTRE);
		
	    // header report
	    WritableCell readCell = sheet.getWritableCell(1, 1);
		CellFormat readFormat = readCell.getCellFormat();
		label = new Label(1, 1, sheet.getCell(1, 1).getContents()+Util.getOrgInfo().getDescription(), readFormat);
		sheet.addCell(label);
		
		// set title
		if(HR_Block_ID != 0)
			label = new Label(1, 2, "Блока "+block.getName(), titleStyle);
		else
			label = new Label(1, 2, "");
		sheet.addCell(label);
		
		X_HR_Employee he = null;
		X_HR_Job job = null;
    	Mhrmeducation hrmedu = new Mhrmeducation(getCtx());
    	lines = hrmedu.getLinesEducation(HR_Block_ID, HR_Department_ID);
    	
    	int col = cellStart.getC()+1; // offset by the number of first columns
		int row = cellStart.getR();
		int dId = 0; // Departmnet ID Merge cells
		int dId_ = 0; // Departmnet ID Percent
		double percentInc = 0; int count = 0; int percentFlag = 0;
		// total percent
		double totalPercent = 0; int totalCount = 1; 
		int dep = 0;
		int bpId = 0; // Business Partner ID
		int jo = 0; int flag = 0;
		int rowC = 0;
		int num = 1;
		int i = 0;
		for(int j = row; j < (row+lines.length); j++){
			//
	    	sheet.setRowView(j+dep, 700);
	    	
			// Title
			he = X_HR_Employee.getHR_Employee(lines[i].getC_BPartner_ID(),true); 
			if(i == 0){
				bpId = lines[i].getC_BPartner_ID();
				dId_ = he.getHR_Department_ID();
			}

			job = new X_HR_Job(getCtx(), he.getHR_Job_ID(), null);
			// Merge cells by Department
			if(dId != he.getHR_Department_ID()){
				
				// outcomes by type of education
			   if(i > 0){
			      try{
			    	  sheet.insertRow(j+dep);
				      sheet.setRowView(j+dep, 500);
				      sheet.mergeCells(1, j+dep, 2, j+dep);
				      //
				      label = new Label(1, j+dep, "с высшим образованием", cellPercent); 
				      sheet.addCell(label);
				      number = new Number(3, j+dep, (percentInc/count), percentf); 
				      sheet.addCell(number);
				      dep++;
				      sheet.insertRow(j+dep);
				      sheet.setRowView(j+dep, 500);
				      sheet.mergeCells(1, j+dep, 2, j+dep);
				      //
				      label = new Label(1, j+dep, "др. образование", cellPercent); 
				      sheet.addCell(label);
				      number = new Number(3, j+dep, (100-(percentInc/count)*100)/100, percentf); 
				      sheet.addCell(number);
				      dep++;
				      sheet.insertRow(j+dep);
				      dep++;
			      }catch(ArithmeticException e){
			    	  System.out.println("ERROR: "+e.getMessage());
			      }			      
			   }
		       percentInc = 0;
		       count=0;
		       percentFlag = 0;
				
		       dId = he.getHR_Department_ID();
		       sheet.insertRow(j+dep);
		       sheet.mergeCells(1, j+dep, 9, j+dep);
		       sheet.setRowView(j+dep, 600);
		       label = new Label(1, j+dep, he.getHR_Department().getName(), cellDepartment); 
			   sheet.addCell(label);
		       dep++;
	    	}
			
			// calculate the number of employees with higher education within the department
			if(dId_ == he.getHR_Department_ID()){
				if(bpId == lines[i].getC_BPartner_ID() && percentFlag == 0){
					if(lines[i].gethrm_educationtype().equals(X_hrm_education.HRM_EDUCATIONTYPE_Higher)){
						percentInc++; percentFlag = 1;
						totalPercent++;
					}
				}else{
					if(bpId != lines[i].getC_BPartner_ID()){
						totalCount++;
						num++;
						if(lines[i].gethrm_educationtype().equals(X_hrm_education.HRM_EDUCATIONTYPE_Higher)){
							percentInc++; percentFlag = 1;
							totalPercent++;
						}
					}
				}
				dId_ = he.getHR_Department_ID();
			}else{
				if(bpId != lines[i].getC_BPartner_ID()){
					totalCount++;
					num++;
					if(lines[i].gethrm_educationtype().equals(X_hrm_education.HRM_EDUCATIONTYPE_Higher)){
						percentInc++; percentFlag = 1;
						totalPercent++;
					}
				}
				dId_ = he.getHR_Department_ID();
				
			} // end calculate
			
			// #
			number = new Number(cellStart.getC(), j+dep, num, borderStyle); 
			sheet.addCell(number);
			// initials
			label = new Label(col, j+dep, lines[i].getC_BPartner().getName(), borderStyle); 
			sheet.addCell(label);
			// Job
			label = new Label(col+1, j+dep, job.getName(), borderStyle); 
			sheet.addCell(label);
			// Education
			label = new Label(col+2, j+dep, lines[i].gethrm_institutionname(), borderStyle); 
			sheet.addCell(label);
			// Type education
			label = new Label(col+3, j+dep, MRefList.getListName(getCtx(), X_hrm_education.HRM_EDUCATIONTYPE_AD_Reference_ID, lines[i].gethrm_educationtype()), borderStyle); 
			sheet.addCell(label);
			// Specialty
			label = new Label(col+4, j+dep, lines[i].gethrm_diplomaspeciality(), borderStyle); 
			sheet.addCell(label);
			// Specialty
			label = new Label(col+5, j+dep, lines[i].gethrm_diplomaqualification(), borderStyle); 
			sheet.addCell(label);
			// Start Year
			number = new Number(col+6, j+dep, lines[i].gethrm_startyear(), borderStyle); 
			sheet.addCell(number);
			// End Year
			number = new Number(col+7, j+dep, lines[i].gethrm_finishyear(), borderStyle); 
			sheet.addCell(number);
			
			// combine the match
	    	if(bpId == lines[i].getC_BPartner_ID())
	    	   if(flag == 0){
		    		flag = j+dep;
		    		count++; 
	    	   }
		    	else
		    		jo++;
	    	else{
	    	   if(flag != 0){
	    		   sheet.mergeCells(1, flag, 1, flag+jo);
	    		   sheet.mergeCells(2, flag, 2, flag+jo);
	    		   sheet.mergeCells(3, flag, 3, flag+jo);
	    		   count++;
	    	   }
	    	   jo = 0;
	    	   flag = j+dep;
	    	}
	    	
	    	bpId = lines[i].getC_BPartner_ID();			
	    	i++;
	    	rowC = j+dep;
		}
		// last cell
		sheet.mergeCells(1, flag, 1, flag+jo);
		sheet.mergeCells(2, flag, 2, flag+jo);
		sheet.mergeCells(3, flag, 3, flag+jo);
	    
	    // the last row of the results by type of education
	    try{
	    	rowC++;
	    	sheet.insertRow(rowC);
		    sheet.setRowView(rowC, 500);
		    sheet.mergeCells(1, rowC, 2, rowC);
		    //
		    label = new Label(1, rowC, "с высшим образованием", cellPercent); 
		    sheet.addCell(label);
		    number = new Number(3, rowC, (percentInc/count), percentf); 
		    sheet.addCell(number);
		    rowC++;
		    sheet.insertRow(rowC);
		    sheet.setRowView(rowC, 500);
		    sheet.mergeCells(1, rowC, 2, rowC);
		    //
		    label = new Label(1, rowC, "др. образование", cellPercent); 
		    sheet.addCell(label);
		    number = new Number(3, rowC, (100-(percentInc/count)*100)/100, percentf); 
		    sheet.addCell(number);
		    rowC++;
		    sheet.insertRow(rowC);
		    rowC++;
		    
		    // total percent
		    rowC+=2;
		    sheet.mergeCells(1, rowC, 3, rowC);
		    sheet.setRowView(rowC, 500);
		    label = new Label(1, rowC, "ИТОГ ПО КОМПАНИИ", cellDepartment); 
		    sheet.addCell(label);
		    rowC++;	
		    sheet.mergeCells(1, rowC, 2, rowC);
		    sheet.setRowView(rowC, 500);
		    label = new Label(1, rowC, "с высшим образованием", cellPercent); 
		    sheet.addCell(label);
		    number = new Number(3, rowC, (totalPercent/totalCount), percentf); 
		    sheet.addCell(number);
		    rowC++;		 
		    sheet.mergeCells(1, rowC, 2, rowC);
		    sheet.setRowView(rowC, 500);
		    label = new Label(1, rowC, "др. образование", cellPercent); 
		    sheet.addCell(label);
		    number = new Number(3, rowC, (100-(totalPercent/totalCount)*100)/100, percentf); 
		    sheet.addCell(number);
		    rowC++;	
	    }catch(ArithmeticException e){
	    	log.log(Level.SEVERE, "ERROR: " + e.getMessage());
	    	System.out.println("ERROR: "+e.getMessage());
	    }	
		
		copy.write();
		copy.close();
	    tableBook.close();
	    temporaryFile.delete();
	       
		// Open the file with the application associated
		String fileName = fullPath.toString();
		Util.launchFile(new File(fileName));
		
		return Msg.translate(m_ctx, "Success");
	}

}
